const config = require('../config');

const mysql = require('../db/mysql57');
const minio = require('../db/minio');

const HIRE_CONTENT = `j.id as id,
o.uid as uid,
u.username as user,
o.name as organizationName,
o.logo as logoId,
j.name as name,
j.description as description,
j.requirements as requirements,
j.benefits as benefits,
j.typeId as typeId,
jt.name as type,
j.positions as positions,
j.location as location,
j.contact as contact,
j.salary as salary,
j.isHot as isHot,
j.isRecommended as isRecommended,
(select COUNT(1) from JobCollect as jc where j.id = jc.jobId and jc.uid = ? LIMIT 1) as isCollect,
(select COUNT(1) from Deliver as d where j.id = d.jobId and d.uid = ? LIMIT 1) as isDeliver,
(select COUNT(1) from Deliver as d where j.id = d.jobId) as deliverCount,
DATE_FORMAT(j.createTime, '%Y-%m-%d %H:%i:%s') as createTime,
DATE_FORMAT(j.endTime, '%Y-%m-%d %H:%i:%s') as endTime`;

const hireDao = {
    async getLogoUrl(logoId) {
        let browser;
        let uri;
        let sql = `select browser, uri from PublicImg where id = ? and status = ? LIMIT 1`;
        let data = [logoId, '已完成'];
        let result = await mysql.sqlExec(sql, data);
        if (result.length) {
            browser = result[0].browser;
            uri = result[0].uri;
        } else {
            return "";
        }
        // 获取图片预处理URL
        try {
            let url = await minio.getDownloadUrl(browser, uri, config.minio.downloadUrlEnableTime);
            if (!url.success) {
                return "";
            }
            return url.url;
        } catch (e) {
            return "";
        }
    },
    getAllJobTypes() {
        let sql = `SELECT id, name FROM JobType`;
        return mysql.sqlExec(sql, []);
    },
    async createOrganization(uid, organization) {
        // 尝试开启事务
        let connection;
        let result;
        let browser = config.minio.organizationLogoBrower;
        let uri = '';
        let logoId;
        // 开启事务
        try {
            connection = await mysql.startTransaction();
        } catch (error) {
            throw error;
        }
        // 往 PublicImg 表中插入数据
        try {
            let sql = `insert into PublicImg (browser, uri, status) VALUES (?, ?, ?)`;
            let data = [ browser, uri, '待上传' ];
            result = await mysql.sqlExecInTransaction(connection, sql, data);
            logoId = result.insertId;
            uri = `${logoId}.jpg`;
        } catch (error) {
            await mysql.rollbackTransaction(connection);
            throw error;
        }
        // 更新 logo 上传状态
        try {
            let sql = `update PublicImg set status = ?, uri = ? where id = ?`;
            let data = ['已完成', uri, logoId];
            await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            await mysql.rollbackTransaction(connection);
            throw err;
        }
        // 创建组织
        try {
            let sql = `insert into Organization (uid, name, description, logo) VALUES (?, ?, ?, ?)`;
            let data = [ uid, organization.name, organization.description, logoId ];
            result = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            await mysql.rollbackTransaction(connection);
            throw err;
        }
        // 上传 logo
        try {
            let buffer = organization.logoBuffer;
            await new Promise((resolve, reject) => {
                minio.minio.putObject(browser, uri, buffer, buffer.length, async function (err, etag) {
                    if (err) {
                        reject(err);
                        return ;
                    } else {
                        resolve(true);
                    }
                });
            });
        } catch (err) {
            await mysql.rollbackTransaction(connection);
            throw err;
        }
        // 提交事务
        try {
            mysql.commitTransaction(connection);
        } catch (error) {
            await mysql.rollbackTransaction(connection);
            throw error;
        }
        // 返回数据
        return result;
    },
    /**
     * 创建一个岗位 
     */
    publishJob(uid, job) {
        let sql = `INSERT INTO Job (
                oid, name, description, requirements,
                benefits, typeId, positions, location,
                contact, salary, isHot, isRecommended,
                endTime
            ) (
                SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
                WHERE (
                    SELECT uid FROM Organization WHERE id = ?
                ) = ?
            )`;
        let data = [
            job.oid, job.name, job.description, job.requirements,
            job.benefits, job.typeId, job.positions, job.location,
            job.contact, job.salary, job.isHot, job.isRecommended,
            job.endTime, job.oid, uid
        ];
        return mysql.sqlExec(sql, data);
    },
    /**
     * 懒加载获取20个推荐岗位接口
     */
    getRecommendJobs(uid, lastId) {
        let data;
        let query;
        if (lastId) {
            query = `jtmp.id < ? AND `;
            data = [ uid, uid, lastId, 'false' ];
        } else {
            query = '';
            data = [ uid, uid, 'false' ];
        }
        let sql = `SELECT
                ${HIRE_CONTENT}
            FROM (
                select * from Job as jtmp
                WHERE ${query}
                    jtmp.isClose = ?
                    AND (
                        jtmp.endTime IS NULL
                        OR jtmp.endTime > CURRENT_TIMESTAMP
                    )
                ORDER BY jtmp.id DESC
                LIMIT ${ config.hire.getRecommendJobsCount }
            ) as j
            LEFT JOIN Organization as o ON j.oid = o.id
            LEFT JOIN User as u ON o.uid = u.id
            LEFT JOIN JobType as jt ON j.typeId = jt.id`;
        return mysql.sqlExec(sql, data);
    },
    getJobInfo(uid, id) {
        let sql = `select
                ${HIRE_CONTENT}
            from (
                select * from Job as jtmp
                where jtmp.id = ?
                    AND jtmp.isClose = ?
                    AND (
                        jtmp.endTime IS NULL
                        OR jtmp.endTime > CURRENT_TIMESTAMP
                    )
                LIMIT 1
            ) as j
            LEFT JOIN Organization as o ON j.oid = o.id
            LEFT JOIN User as u ON o.uid = u.id
            LEFT JOIN JobType as jt ON j.typeId = jt.id`;
        let data = [ uid, uid, id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    collectJob(uid, id, collect) {
        let sql;
        let data;
        if (collect) {
            sql = `INSERT INTO JobCollect (uid, jobId) (select ?, ? where NOT EXISTS (SELECT 1 FROM JobCollect WHERE uid = ? AND jobId = ?))`;
            data = [ uid, id, uid, id ];
        } else {
            sql = `DELETE FROM JobCollect WHERE jobId = ? AND uid = ?`;
            data = [ id, uid ];
        }
        return mysql.sqlExec(sql, data);
    },
    submit(uid, id) {
        let sql = `INSERT INTO Deliver (uid, jobId) (select ?, ? where NOT EXISTS (SELECT 1 FROM Deliver WHERE uid = ? AND jobId = ?))`;
        let data = [ uid, id, uid, id ];
        return mysql.sqlExec(sql, data);
    }
};

module.exports = hireDao;
